# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html


import pymysql.cursors
from jianshu.items import JianshuUserItem, JianShuArticle, JianShuArticleDetail



class JianshuPipeline(object):

    url = '118.89.193.59'
    port = 3306
    database_username = 'root'
    database_password = '123123anquan'
    database_name = 'JianShu'
    user_collection = 'User'
    article_collection = 'Article'

    connection = ''

    def open_spider(self, spider):
        
        pass

    def process_item(self, item, spider):
        if (isinstance(item, JianshuUserItem)):
            self.insert_user_item(item)
            pass
        if (isinstance(item, JianShuArticle)):
            self.insert_article_item(item)
            pass
        if (isinstance(item, JianShuArticleDetail)):
            self.update_jianshuDetail(item)
            pass

        return item


    def close_spider(self, spider):

        pass

    def insert_user_item(self,item):

        try:
            self.connection = pymysql.connect(host=self.url,
                                        port=self.port,
                                        user=self.database_username,
                                        password=self.database_password,
                                        db=self.database_name,
                                        charset='utf8mb4',
                                        cursorclass=pymysql.cursors.DictCursor)
            with self.connection.cursor() as cursor:
                sql = "INSERT INTO `User` (`name`, `user_token`, `avatar`) VALUES (%s, %s, %s)"
                cursor.execute(sql, (item['name'], item['user_token'], item['avatar']))
                cursor.close()
                self.connection.commit()
                self.connection.close()
        except Exception as e:
            print('插入用户错误')
            print(str(e))
        finally:
            print('插入用户结束')
        pass

    def insert_article_item(self,item):
        try:
            self.connection = pymysql.connect(host=self.url,
                                        port=self.port,
                                        user=self.database_username,
                                        password=self.database_password,
                                        db=self.database_name,
                                        charset='utf8mb4',
                                        cursorclass=pymysql.cursors.DictCursor)
            with self.connection.cursor() as cursor:
                sql = "INSERT INTO `Article` (`article_token`, `title`, `user_token`, `expert`, `time`, `small_image`) VALUES (%s, %s, %s, %s, %s, %s)"
                cursor.execute(sql, (item['article_token'], item['title'], item['user_token'], item['expert'], item['time'], item['small_image']))
                cursor.close()
                self.connection.commit()
                self.connection.close()
        except Exception as e:
            print('插入文章错误')
            print(str(e))
        finally:
            print('插入文章结束')
        pass

    def update_jianshuDetail(self, item):
        try:
            self.connection = pymysql.connect(host=self.url,
                                        port=self.port,
                                        user=self.database_username,
                                        password=self.database_password,
                                        db=self.database_name,
                                        charset='utf8mb4',
                                        cursorclass=pymysql.cursors.DictCursor)
            with self.connection.cursor() as cursor:
                sql = "UPDATE Article SET article_detail=%s WHERE article_token=%s"
                cursor.execute(sql, (item["article_detail"], item["article_token"]))
                self.connection.commit()
                self.connection.close()
        except Exception as e:
            print('插入详情失败')
            print(str(e))

        finally:
            print('插入详情结束')
        pass